const mysql = require('mysql2')
const mysql2 = require('mysql2/promise');

// 连接池优化数据库连接
(async function() {
    // 执行SQL自动连接池取连接, 用完后自动放回去
    const pool = mysql2.createPool({
        host: 'localhost',
        user: 'root',
        password: '',
        database: 'practice',
        waitForConnections: true, // true没可用连接则等待, false直接返回报错
        connectionLimit: 10, // 限制连接数
        maxIdle: 10, // 限制空闲数, 超过则空闲连接释放 
        idleTimeout: 60000, // 空闲连接多久断开
        queueLimit: 0, // 排队请求的数量, 超过则报错, 0则无上限
        enableKeepAlive: true, // 保持心跳设置, 默认即可
        keepAliveInitialDelay: 0 // 保持心跳设置, 默认即可
    });
    // const [results] = await pool.query('select * from customers');
    // console.log(results);

    // 增删改查时手动取连接
    const connection = await pool.getConnection();
    const [results] = await connection.query('select * from orders');
    console.log(results);
})();


// Promise用法(创建连接)
(async function() {
    const connection = await mysql2.createConnection({
        host: 'localhost',
        port: 3306,
        user: 'root',
        password: '',
        database: 'practice'
    });
    const [results, fields] = await connection.query('SELECT * FROM customers');
    console.log(results);
    console.log(fields.map(item => item.name)); 
})();


// 函数用法(创建连接)
const connection = mysql.createConnection({
  host: 'localhost',
  port: 3306,
  user: 'root',
  password: '',
  database: 'practice',
})


// 查询数据
connection.query('SELECT * FROM customers', (err, results, fields) => {
  console.log(results)
  console.log(fields.map((item) => item.name))
})
connection.query('SELECT * FROM customers WHERE name LIKE ?', ['李%'], (err, results, fields) => {
    console.log(results);
    console.log(fields.map(item => item.name)); 
});
// 增加数据
connection.execute('INSERT INTO customers (name) VALUES (?)', ['光'], (err, results, fields) => {
    console.log(err);
});
// 更改数据
connection.execute('UPDATE customers SET name="guang" where name="光"', (err) => {
    console.log(err);
});
// 删除数据
connection.execute('DELETE  FROM customers where name=?', ['guang'], (err) => {
    console.log(err);
});

